<?php 
$req_path="./";
include_once($req_path."include/ini.php");
require_once($req_path."include/db.php");

class M_service extends DB{
	private $dbConn;
	private $rowCount="0";
	private $ID="0";
	private $sqlSelect ="select * from tb_product where 1=1 and type=4 ";
	private $totalpage="0";
	
	function M_service(){
		parent::__construct();
	}
	function getListPage(){
		global $list_page;
		return $list_page;
	}
	function getTotalPage(){
		return $this->totalpage;
	}
	function getData($sname=null, $ename=null,$pageStart=1,$id=null){
		global $list_page;
		
		$sqlscript = "SELECT s.ID, s.ProductID, P.ProductName,u.FirstName,COALESCE(u.LastName,'') as LastName, po.PositionName, s.Commission 
						, CASE WHEN s.CommissionType = 0 THEN ' ฿' ELSE ' %' END ComsType
						FROM tb_product p
						JOIN tb_product_service_commission s ON s.ProductID = p.ID
						JOIN tb_user u ON u.UserID = s.UserID AND u.IsActived=1
						JOIN tb_Position po ON po.ID = u.PositionID AND po.IsDeleted=0
						WHERE Type=4 AND p.IsDeleted=0 and s.IsDeleted=0";
		 
		if (isset($sname) && $sname!=""){
			$sqlscript.= " and ProductName Like '%". $sname."%'";
		}
		 if (isset($ename) && $ename!=""){
			$sqlscript.= " and (u.FirstName Like '%". $ename."%' or u.LastName like '%".$ename ."%' )" ;
		}
		if (isset($id) && $id!=""){
			$sqlscript.= " and u.ID =". $id;
		}
		
		$sqlscript.= " order by p.ProductName asc ";
		//Execute
		$result = $this->ExecuteData($sqlscript);
	 
		//Get all rows
		$rows =$result->RecordCount();
	
		//Page limit
		$sqlscript.= " limit ". ($pageStart-1)*$list_page. ", " . $list_page;
		//paging
		$result = $this->ExecuteData($sqlscript);		

		$totalpage=1;
		if ($rows>0){
			$rt = $rows%$list_page;
			
			if($rt!=0) {
				$totalpage = floor($rows/$list_page)+1;
			}
			else {
				$totalpage = floor($rows/$list_page);
			}
		}
		
		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;
	}
	
	function getDataDetail($id=0){
		$sqlscript= "Select u.ID, ProductName, Category, c.CatName,u.Type "
				.", t.Type TypeName ,u.Type as ProductType"
				.", un.Unit, u.IsPOS, u.IsStock, IsPremium , u.LimitNumber ,u.Times  "
				.", u.Price "
				.", u.MainUnit "
				.", u.Commission   "
				.", CASE WHEN u.CommissionType = 0 THEN ' ฿' ELSE ' %' END ComsType "
				.",  u.CommissionType   "
				.", u.SubUnit "
				.", u.Scale,Cost_Price ,Cost_branch "
				." From tb_product u "
				." LEFT JOIN tb_product_category c on u.category=c.CatID "
				." LEFT JOIN tb_units un on u.MainUnit=un.ID "
				." LEFT JOIN tb_product_type t on t.ID=u.Type "
				." Where 1=1 and u.IsDeleted=0 and u.ID=".$id;
				
		$result = $this->ExecuteData($sqlscript);
		return $result->FetchRow();
	}
	//get product list
	function getProductList($id=0){
		$sqlscript= "Select d.SubProductID as ID, p.ProductName, d.Amount  "
				. " from tb_product_details d "
				. " join tb_product p on p.ID = d.SubProductID where p.IsDeleted =0 and d.ProductID=".$id;

		$result = $this->ExecuteData($sqlscript);
		return $result;
	}
	function getRowCount(){
		return $this->rowCount;
	}
	
	function getLastInsert(){
		return $this->ID;
	}
	
	function InsertData($val){
		if (isset($val)){
			$sqlInsert=$this->InsertSqlScript($this->sqlSelect, $val);
			$this->ExecuteData($sqlInsert);
			$this->ID = $this->getLastID();
			return $this->ID;
		}
	}
	function updateData($sql, $val){
		if(isset($val)){
			$sqlUpdate=$this->UpdateSqlScript($sql,$val);
			return $this->ExecuteData($sqlUpdate);
		}
	}
	function delData($id){
		if(isset($id)){
			$sqlDelete = "update tb_product_service_commission set IsDeleted=1 where ID='".$id."'";
			$this->ExecuteData($sqlDelete);
			//$this->delProductDetail($id);
			return "c";
		}
	}
	function delProductDetail($id){
		$sqlDelete = "delete from tb_product_details where ProductID='".$id."'";
		$this->ExecuteData($sqlDelete);
	}
	
	function Excute($sql){
		$this->ExecuteData($sql);
	}
	function getDataCBO($t="c"){
		if ($t=="c")
			$sqlscript= "select CatID as ID, CatName as Name from tb_product_category where IsDeleted=0 ";
		else if ($t=="u")
			$sqlscript= "select ID as ID, Unit as Name  from tb_units where 1=1 ";
		else if ($t=="p")	{
			$sqlscript= "select ID as ID, ProductName as Name from tb_product where 1=1 and IsDeleted=0 and Type=1 and IsPOS=1 order by ProductName ";
			
			$result = $this->ExecuteData($sqlscript);
			while($row = $result->FetchRow()){
				$ar[$row["Name"]] = $row["ID"];
			}
			return $ar;	
			
		}else if ($t=="tr") {// treatment
			$sqlscript= "select ID as ID, ProductName as Name from tb_product where 1=1 and IsDeleted=0 and Type=2 order by ProductName";

			$result = $this->ExecuteData($sqlscript);
			while($row = $result->FetchRow()){
				$ar[$row["Name"]] = $row["ID"];
			}
			return $ar;						
			
		}else if ($t=="ts") {// get treatment & set
			$sqlscript= "select ID as ID, ProductName as Name from tb_product where 1=1 and IsDeleted=0 and Type IN (2,3)  order by ProductName";
			$result = $this->ExecuteData($sqlscript);
			while($row = $result->FetchRow()){
				$ar[$row["Name"]] = $row["ID"];
			}
			return $ar;			
		
		}
		else if ($t=="s"){ // get set
			$sqlscript= "select ID as ID, ProductName as Name from tb_product where 1=1 and IsDeleted=0 and Type =3 order by ProductName";	
			
			$result = $this->ExecuteData($sqlscript);
			while($row = $result->FetchRow()){
				$ar[$row["Name"]] = $row["ID"];
			}
			return $ar;			
		}else if ($t=="co") {// get course
			$sqlscript= "select ID as ID, ProductName as Name from tb_product where 1=1 and IsDeleted=0 and Type =4 order by ProductName";						
			
			$result = $this->ExecuteData($sqlscript);
			while($row = $result->FetchRow()){
				$ar[$row["Name"]] = $row["ID"];
			}
			return $ar;
			
		}else if ($t=="t")	
			$sqlscript= "select ID as ID, Type as Name from tb_product_type where 1=1 ";
		else if ($t=="tn")	
			$sqlscript= "select ID as ID, Type as Name from tb_product_type where 1=1 and ID <>4 ";			
		else if ($t=="b")	{
			$sqlscript= "select ID as ID, BranchName as Name from tb_branch where IsActived=1 and 1=1 order by  BranchName ";			
			
			$result = $this->ExecuteData($sqlscript);
			while($row = $result->FetchRow()){
				$ar[$row["Name"]] = $row["ID"];
			}
			return $ar;
			
		}
		else if ($t=="cc")	
			$sqlscript= "select ID as ID, Name as Name from tb_coupon where IsDeleted=0 and  type=0 and DATE_FORMAT(Now(), '%Y/%m/%d' ) between EffectiveDateFrom  and EffectiveDateTo  order by ID ";			
		else if ($t=="cp")	
			$sqlscript= "select ID as ID, Name as Name from tb_coupon where IsDeleted=0 and  type=1 and DATE_FORMAT(Now(), '%Y/%m/%d' ) between EffectiveDateFrom  and EffectiveDateTo order by ID ";			
		else if ($t=="rp")	{
			$sqlscript= "select ID as ID, ProductName as Name from tb_product where 1=1 and IsDeleted=0 and Type=1 and IsPOS=1  and category <> 20 order by ProductName ";
			
			$result = $this->ExecuteData($sqlscript);
			while($row = $result->FetchRow()){
				$ar[$row["Name"]] = $row["ID"];
			}
			return $ar;
		 }
	
		 
		$result = $this->ExecuteData($sqlscript);
		
		 while($row = $result->FetchRow()){
			$ar[$row["ID"]] = $row["Name"];
		 }
		 
		 return $ar;
	}

}
?>